package com.market.dao.jpa.report;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.market.dao.LocationDao;
import com.market.domain.Location;
import com.market.domain.Order;
import com.market.domain.Product;
import com.market.domain.transients.OrderQuery;
import com.market.domain.transients.SalesGroupByProductQuery;
import com.market.domain.transients.SalesGroupByUserQuery;
import com.market.domain.transients.report.OrderDetailSummaryByProduct;
import com.market.domain.transients.report.SalesGroupByProduct;
import com.market.domain.transients.report.SalesGroupByUser;
import com.market.domain.transients.report.UserRecord;
import com.market.domain.transients.report.WhoBuyIt;

@Repository("reportFactory")
public class ReportFactory {

	/**
	 * Log variable for all child classes. Uses LogFactory.getLog(getClass())
	 * from Commons Logging
	 */
	protected final Log log = LogFactory.getLog(this.getClass());

	@PersistenceContext
	protected EntityManager em;

	@Autowired
	private LocationDao locationDao;

	public List<WhoBuyIt> findWhoBuyIt(Product product) {
		String sql = "select user.id, concat(user.lastname, ' ' ,user.firstName), user.email, user.telephone, "
				+ " sum(orderdetail.orderedquantity),sum(orderdetail.deliveredquantity) as deliveredsum from user, orders,"
				+ " orders_orderdetail, orderdetail where user.id = orders.user_id and orders.id = orders_orderdetail.orders_id "
				+ " and orders_orderdetail.orderdetails_id = orderdetail.id and orderdetail.product_id = '"
				+ product.getId()
				+ "' group by user.id order by deliveredsum desc";
		Query q = this.em.createNativeQuery(sql);
		List<Object[]> resultList = q.getResultList();
		List<WhoBuyIt> whoBuyIts = new ArrayList<WhoBuyIt>();
		for (Object[] objects : resultList) {
			whoBuyIts.add(new WhoBuyIt(((Number) objects[0]).longValue(), (String) objects[1],
					(String) objects[2], (String) objects[3],
					((Number) objects[4]).longValue(), ((Number) objects[5]).longValue()));
		}
		return whoBuyIts;

	}

	public List<UserRecord> findUserRecords(SalesGroupByProductQuery query) {
		String sql = "select  user.lastname, user.firstname, user.email, user.telephone, useractivityrecord.recorddate, "
				+ " useractivityrecord.type, useractivityrecord.comments from user,useractivityrecord, user_useractivityrecord "
				+ " where user.id=user_useractivityrecord.user_id and useractivityrecord.id=user_useractivityrecord.useractivityrecords_id and "
				+ " useractivityrecord.recordDate between '"
				+ query.getStartDateLabel()
				+ "' and '"
				+ query.getEndDateLabel() + "'";

		Query q = this.em.createNativeQuery(sql);

		List<Object[]> resultList = q.getResultList();
		List<UserRecord> userRecords = new ArrayList<UserRecord>();

		for (Object[] objects : resultList) {
			userRecords.add(new UserRecord((String) objects[0],
					(String) objects[1], (String) objects[2],
					(String) objects[3], (Date) objects[4],
					(String) objects[5], (String) objects[6]));
		}

		return userRecords;
	}

	public List<SalesGroupByProduct> findSalesGroupByProduct(
			SalesGroupByProductQuery query) {
		String sql = "select product.id, product.chinesename, product.type, sum(orderdetail.orderedquantity), "
				+ "sum(orderdetail.orderedquantity*orderdetail.orderedprice) as orderedTotalAmount, "
				+ "sum(orderdetail.deliveredquantity), sum(orderdetail.deliveredquantity*orderdetail.deliveredprice) as deliveredTotalAmount "
				+ "from product,orders, orders_orderdetail,orderdetail where orders.id = orders_orderdetail.orders_id and "
				+ "orders_orderdetail.orderdetails_id = orderdetail.id and product.id=orderdetail.product_id  "
				+ "and orders.requireddate between '"
				+ query.getStartDateLabel()
				+ "' and '"
				+ query.getEndDateLabel()
				+ "' and orders.status !='CANCELED' group by product_id order by deliveredTotalAmount desc";

		Query q = this.em.createNativeQuery(sql);
		List<Object[]> resultList = q.getResultList();
		List<SalesGroupByProduct> sales = new ArrayList<SalesGroupByProduct>();
		for (Object[] objects : resultList) {
			sales.add(new SalesGroupByProduct(
					((Number) objects[0]).longValue(),(String) objects[1], (String) objects[2],
					((Number) objects[3]).longValue(), ((Double) objects[4])
							.doubleValue(), ((Number) objects[5]).longValue(),
					((Double) objects[6]).doubleValue()));
		}
		return sales;
	}

	public List<SalesGroupByUser> findSalesGroupByUser(
			SalesGroupByUserQuery query) {
		String sql = "select user.id, user.firstname, user.lastname, user.email, user.telephone, sum(paidamount) as total "
				+ " from orders, user where orders.user_id = user.id and requireddate between '"
				+ query.getStartDateLabel()
				+ "' and '"
				+ query.getEndDateLabel()
				+ "' group by user.email order by total desc";
		Query q = this.em.createNativeQuery(sql);
		List<Object[]> resultList = q.getResultList();
		List<SalesGroupByUser> sales = new ArrayList<SalesGroupByUser>();
		for (Object[] objects : resultList) {
			sales.add(new SalesGroupByUser(((Number) objects[0]).longValue(),
					(String) objects[1], (String) objects[2],
					(String) objects[3], (String) objects[4],
					((Double) objects[5]).doubleValue()));
		}
		return sales;
	}

	public List<OrderDetailSummaryByProduct> findOrderDetailSummaryByProduct(
			OrderQuery query) {
		String sql = "select product.id as id, product.type as type, product.storageLocation as location, product.number as number,"
				+ " product.name as name,  product.chineseName as chineseName, product.purchasePrice as purchasePrice,product.inventory as inventory, "
				+ " sum(orderdetail.orderedquantity) as orderedquantity, "
				+ " sum(orderdetail.orderedquantity*orderdetail.specification*orderdetail.orderedprice) as orderedSubtotal, "
				+ " sum(orderdetail.confirmedquantity) as confirmedquantity, "
				+ " sum(orderdetail.confirmedquantity*orderdetail.specification*orderdetail.confirmedprice) as confirmedSubtotal, "
				+ " sum(orderdetail.deliveredquantity) as deliveredquantity, "
				+ " sum(orderdetail.deliveredquantity*deliveredprice) as deliveredSubTotal,"
				+ " (product.inventory-sum(orderdetail.orderedquantity)) as ordereddifference, "
				+ " (product.inventory-sum(orderdetail.confirmedquantity)) as confirmeddifference,"
				+ " product.priceUnit as unit, product.chinesepriceunit as chineseunit, product.frenchpriceunit as frenchunit, "
				+ " product.specificationDesc as specDesc from orders, orders_orderdetail, orderdetail, product where orders.status != 'CANCELED' and "
				+ " orders.id = orders_orderdetail.orders_id and orders_orderdetail.orderdetails_id = orderdetail.id and "
				+ " orderdetail.product_id = product.id and orders.requiredDate = '"
				+ query.getRequiredDateLabel()
				+ "'  group by id order by location, type";
		Query q = this.em.createNativeQuery(sql);
		List<Object[]> resultList = q.getResultList();
		List<OrderDetailSummaryByProduct> reportDetail = new ArrayList<OrderDetailSummaryByProduct>();
		for (Object[] objects : resultList) {
			reportDetail.add(new OrderDetailSummaryByProduct(
					((Number) objects[0]).longValue(), (String) objects[1],
					(String) objects[2], (String) objects[3],
					(String) objects[4], (String) objects[5],
					((Double) objects[6]).doubleValue(), 
					((Number) objects[7]).intValue(), ((Number) objects[8])
							.intValue(), ((Double) objects[9]).doubleValue(),
					((Number) objects[10]).intValue(), ((Double) objects[11])
							.doubleValue(), ((Double) objects[12])
							.doubleValue(), ((Double) objects[13])
							.doubleValue(), ((Number) objects[14]).intValue(),
					((Number) objects[15]).intValue(), (String) objects[16],
					(String) objects[17], (String) objects[18],
					(String) objects[19]));
		}
		return reportDetail;
	}

	/**
	 * This method is use from print delivery report and prepare report, In case
	 * network or other problem, We can not prepare orders online. We can use
	 * this printed edition to prepare order
	 * 
	 * @param query
	 * @return All locations have orders, and all their orders;
	 */

	public List<Location> findLocationHaveOrder(OrderQuery query) {
		List<Location> allLocations = locationDao.findAll();
		List<Location> locationsHaveOrder = new ArrayList<Location>();
		for (int i = 0; i < allLocations.size(); i++) {
			String sql = "select orders.* from orders, user where orders.status='CONFIRMED' and  orders.user_id = user.id and orders.requiredDate= '"
					+ query.getRequiredDateLabel()
					+ "' and user.dropOffLocation_id = "
					+ allLocations.get(i).getId() + " order by orders.id asc";
			Query q = this.em.createNativeQuery(sql, Order.class);
			List<Order> orders = q.getResultList();
			if (orders.size() > 0) {
				allLocations.get(i).setOrders(orders);
				locationsHaveOrder.add(allLocations.get(i));
			}
		}
		return locationsHaveOrder;
	}
}
